package com.mall.b2c.controller;

import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.mall.b2c.domain.goods.GoodsDomain;
import com.mall.b2c.service.GoodsService;
import com.mall.b2c.util.JsonDataGridHelper;
import com.mall.common.bean.Pager;
import com.mall.common.bean.SearchFilter;
import com.mall.common.controller.BaseController;
import com.mall.common.util.ExcelUtil;
import com.mall.common.web.Servlets;

/**
 * 交易监管
 * @author wangzx
 *
 */
/*@Controller
@RequestMapping("/admin/b2c/supervise")*/
public class SuperviseController extends BaseController {
	@Resource private JdbcTemplate jdbcTemplate;
	@Resource private GoodsService goodsService;

	/**
	 * 跳转到商品列表页面
	 */
	@RequestMapping(value = "/goodsList", method = RequestMethod.GET)
	public String goodsList(Model model) {
		return "/admin/b2c/supervise/goodsList";
	}

	/**
	 * 获取商品列表信息
	 */
	@RequestMapping(value = "/queryGoodsData", method = RequestMethod.POST)
	@ResponseBody
	public Pager queryGoodsData(Pager pager, HttpServletRequest request) {
		Map<String, Object> filterParams = Servlets
				.getParametersStartingWith(request);
		List<SearchFilter> filters = SearchFilter.parse(filterParams);
		pager = goodsService.findPage(pager, filters);
		return JsonDataGridHelper.createJSONData(pager);
	}

	/**
	 * 跳转到详情页面
	 */
	@RequestMapping(value = "/goodsDetail", method = RequestMethod.GET)
	public String goodsDetail(Long id, Model model) {
		GoodsDomain goods = goodsService.find(id);
		model.addAttribute("goods", goods);
		return "/admin/b2c/supervise/goodsDetail";
	}
	
	
	private List<Map<String, Object>> queryData(Pager pager, List<SearchFilter> filters, boolean isSummary, boolean isDrug){
		StringBuffer sb = null;
		Integer totalCount = null;
		Map<String, Object> params = new HashMap<String, Object>();

		if(isSummary){
			sb = new StringBuffer("from (select bsd.productErpId,sum(bsd.quantity) as total,sum(bsd.taxAmount) as totalAmount from b2c_sale_detail bsd group by bsd.productErpId) t,b2c_sale_detail bean where bean.productErpId = t.productErpId");
			sb.append(" and bean.productErpId in (select bg.erpId from b2c_goods bg,b2c_goods_category bgc where bg.goods_category_id = bgc.id and bgc.code ").append(isDrug?"not":"").append(" like '07.%')");
		}else{
			sb = new StringBuffer("from b2c_sale_detail bean where bean.productErpId in (select bg.erpId from b2c_goods bg,b2c_goods_category bgc where bg.goods_category_id = bgc.id and bgc.code ").append(isDrug?"not":"").append(" like '07.%')");
		}
		for(SearchFilter filter : filters){
			String operate = null;
			String fieldName = filter.operator.name() + "_" + filter.fieldName;
			switch(filter.operator){
				case isNull : sb.append(" and ").append(filter.getFieldName()).append(" is null");break;
				case isNotNull : sb.append(" and ").append(filter.getFieldName()).append(" is not null");break;
				case GE : operate = ">="; break;
				case EQ : operate = "=";break;
				case NE : operate = "!=";break;
				case GT : operate = ">";break;
				case GTE: operate = ">=";break;
				case LE : operate = "<=";break;
				case LT : operate = "<";break;
				case LIKE: sb.append(" and ").append(filter.getFieldName()).append(" like :").append(fieldName).append("");params.put(fieldName, "%" + filter.value + "%");break;
				case LTE : operate = "<=";break;
				case ENUM: break;
				default  : sb.append(" and ").append(filter.getFieldName()).append(" in (:").append(fieldName).append(")");params.put(fieldName, filter.value);;
			}
			if(StringUtils.isNotBlank(operate)){
				sb.append(" and ").append(filter.getFieldName()).append(operate).append(":").append(fieldName);
				params.put(fieldName, filter.value);
			}
		}
		
		String countSql = sb.toString();
		
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		if(isSummary){
			totalCount = namedParameterJdbcTemplate.queryForObject("select count(distinct bean.productErpId) " + countSql, params, Integer.class);
		}else{
			totalCount = namedParameterJdbcTemplate.queryForObject("select count(1) " + countSql, params, Integer.class);
		}
		
		if(totalCount != null && totalCount > 0){
			if(isSummary){
				sb.insert(0, "select t.total,t.totalAmount,bean.* ");
			}else{
				sb.insert(0, "select bean.*,DATE_FORMAT(bean.validDate,'%Y-%m-%d') as vDate ");
				sb.append(" order by bean.turnoverTime desc");
			}
			if(pager != null){
				pager.setTotalCount(totalCount);
				sb.append(" limit ").append((pager.getFirstResult())).append(",").append(pager.getPageSize());
			}
			List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sb.toString(), params);
			return list;
		}
		return new ArrayList<Map<String, Object>>();
	}
	
	@RequestMapping("/{type}")
	public String detail(@PathVariable String type, Model model){
		model.addAttribute("type", type);
		return "/admin/b2c/supervise/detail";
	}
	
	@RequestMapping("/summary/{type}")
	public String summary(@PathVariable String type, Model model){
		model.addAttribute("type", type);
		return "/admin/b2c/supervise/summary";
	}

	@RequestMapping({"/{type}/queryData","/summary/{type}/queryData"})
	@ResponseBody
	public Pager queryDetailData(@PathVariable String type, Pager pager, HttpServletRequest request){
    	Map<String, Object> filterParams = Servlets.getParametersStartingWith(request);
		List<SearchFilter> filters = SearchFilter.parse(filterParams);
		
		pager.setList(queryData(pager, filters, request.getRequestURI().contains("/summary"), "drug".equals(type)));
		return JsonDataGridHelper.createJSONData(pager);
	}
	
	@RequestMapping({"/{type}/download", "/summary/{type}/download"})
	public ModelAndView download(@PathVariable String type, HttpServletRequest request){
		Map<String, Object> filterParams = Servlets.getParametersStartingWith(request);
		List<SearchFilter> filters = SearchFilter.parse(filterParams);
		boolean isSummary = request.getRequestURI().contains("/summary");
		List<Map<String, Object>> list = queryData(null, filters, isSummary, "drug".equals(type));
		return new ModelAndView(new ExcelView(list, isSummary, "drug".equals(type)?"药品销售" : "医疗器械销售"));
	}
	
	class ExcelView extends AbstractExcelView{
		
		private List<Map<String, Object>> list;
		private boolean isSummary;
		private String fileName;

		public ExcelView(List<Map<String, Object>> list, boolean isSummary,
				String fileName) {
			super();
			this.list = list;
			this.isSummary = isSummary;
			this.fileName = fileName;
		}

		@Override
		protected void buildExcelDocument(Map<String, Object> model,
				HSSFWorkbook workbook, HttpServletRequest request,
				HttpServletResponse response) throws Exception {
			String[] headers = null;
			String[] fields = null;
			ExcelUtil excelUtil = ExcelUtil.getInstance();
			CellStyle titleStyle = excelUtil.setCellStyle(workbook, ExcelUtil.POSITION_TITLE);
			CellStyle bodyStyle = excelUtil.setCellStyle(workbook, ExcelUtil.POSITION_BODY);
			CellStyle bodyIntStyle = excelUtil.setCellStyle(workbook, ExcelUtil.POSITION_BODY_INT);
			CellStyle bodyDecimalStyle = excelUtil.setCellStyle(workbook, ExcelUtil.POSITION_BODY_DECIMAL);
			if(isSummary){
				headers = new String[]{"产品名称","规格","批准文号","合计数量","合计金额（元）","生产厂家","供货单位"};
				fields = new String[]{"productName","productSpec","authorizeNumber","total","totalAmount","manufacturer","provider"};	
			}else{
				headers = new String[]{"购买者","产品名称","规格","销售价（元）","数量","销售地区","销售时间","批号","有效期","生产厂家"};
				fields = new String[]{"consignee","productName","productSpec","taxPrice","quantity","consigneeArea","turnoverTime","batch","validDate","manufacturer"};
			}
			HSSFSheet sheet = workbook.createSheet();
			for(int i = 0;i < headers.length;i++){
				HSSFCell cell = getCell(sheet, 0, i);
				cell.setCellValue(headers[i]);
				cell.setCellStyle(titleStyle);
				sheet.setColumnWidth(i, 5000); // 设置每列的宽度
			}
			if(list != null){
				for(int i = 0;i < list.size();i++){
					Map<String, Object> map = list.get(i);
					for(int j = 0;j < fields.length;j++){
						HSSFCell cell = getCell(sheet, i + 1, j);
						Object data = map.get(fields[j]);
						if(data == null){
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellStyle(bodyStyle);
							continue;
						}
						if(data instanceof Integer){
							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
							if(!StringUtils.isEmpty(data.toString())){
								cell.setCellValue(new Integer(data.toString()));
							}
							cell.setCellStyle(bodyIntStyle);
						}else if(data instanceof Number){
							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
							if(!StringUtils.isEmpty(data.toString())){
								cell.setCellValue(new Double(data.toString()));
							}
							cell.setCellStyle(bodyDecimalStyle);
						}else{
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(data.toString());
							cell.setCellStyle(bodyStyle);
						}
					}
				}
			}

			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + (isSummary?"汇总":"明细"), "UTF8"));
			response.setContentType("application/octet-stream; charset=utf-8");  
		}
		
	}
	
}